package org.skydiveapplibrary.data;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public final class Database extends SQLiteOpenHelper
{
	// database constants
	public static final String DATABASE_NAME = "SkydiveAppDatabase";
	public static final int DATABASE_VERSION = 3;

	// table constants
	// log entry table
	public static final String LOG_ENTRY_TABLE = "log_entry";
	public static final String LOG_ENTRY_ID = "_id";
	public static final String LOG_ENTRY_JUMP_NUMBER = "jump_number";
	public static final String LOG_ENTRY_DATE = "date";
	public static final String LOG_ENTRY_DISTANCE_TO_TARGET = "distance_to_target";
	public static final String LOG_ENTRY_ALTITUDE_UNIT = "altitude_unit";
	public static final String LOG_ENTRY_EXIT_ALTITUDE = "exit_altitude";
	public static final String LOG_ENTRY_DEPLOYMENT_ALTITUDE = "deployment_altitude";
	public static final String LOG_ENTRY_SKYDIVE_TYPE = "skydive_type";
	public static final String LOG_ENTRY_LOCATION = "location";
	public static final String LOG_ENTRY_AIRCRAFT = "aircraft";
	public static final String LOG_ENTRY_FREEFALL_TIME = "freefall_time_seconds";
	public static final String LOG_ENTRY_CUTAWAY = "cutaway";
	public static final String LOG_ENTRY_NOTES = "notes";
	public static final String LOG_ENTRY_LAST_MODIFIED_UTC = "last_modified_utc";
	public static final String LOG_ENTRY_LAST_SIGNATURE_UTC = "last_signature_utc";
	public static final String LOG_ENTRY_UNIQUE_ID = "unique_id";
	public static final String[] LOG_ENTRY_ALL_COLUMNS =
	{
		LOG_ENTRY_ID,
		LOG_ENTRY_JUMP_NUMBER,
		LOG_ENTRY_DATE,
		LOG_ENTRY_DISTANCE_TO_TARGET,
		LOG_ENTRY_ALTITUDE_UNIT,
		LOG_ENTRY_EXIT_ALTITUDE,
		LOG_ENTRY_DEPLOYMENT_ALTITUDE,
		LOG_ENTRY_SKYDIVE_TYPE,
		LOG_ENTRY_LOCATION,
		LOG_ENTRY_AIRCRAFT,
		LOG_ENTRY_FREEFALL_TIME,
		LOG_ENTRY_CUTAWAY,
		LOG_ENTRY_NOTES,
		LOG_ENTRY_LAST_MODIFIED_UTC,
		LOG_ENTRY_LAST_SIGNATURE_UTC,
		LOG_ENTRY_UNIQUE_ID
	};
	
	// signature table
	public static final String SIGNATURE_TABLE = "signature";
	public static final String SIGNATURE_ID = "_id";
	public static final String SIGNATURE_FILENAME = "filename";
	public static final String SIGNATURE_MD5 = "md5";
	public static final String SIGNATURE_LICENSE = "license";
	public static final String[] SIGNATURE_ALL_COLUMNS =
	{
		SIGNATURE_ID,
		SIGNATURE_FILENAME,
		SIGNATURE_MD5,
		SIGNATURE_LICENSE
	};
	
	// log_entry-signature table
	public static final String LOG_ENTRY_SIGNATURE_TABLE = "log_entry_signature";
	public static final String LOG_ENTRY_SIGNATURE_ID = "_id";
	public static final String LOG_ENTRY_SIGNATURE_LOG_ENTRY = "log_entry";
	public static final String LOG_ENTRY_SIGNATURE_SIGNATURE = "signature";
	public static final String[] LOG_ENTRY_SIGNATURE_ALL_COLUMNS =
	{
		LOG_ENTRY_SIGNATURE_ID,
		LOG_ENTRY_SIGNATURE_LOG_ENTRY,
		LOG_ENTRY_SIGNATURE_SIGNATURE
	};
	
	// log_entry-rig table
	public static final String LOG_ENTRY_RIG_TABLE = "log_entry_rig";
	public static final String LOG_ENTRY_RIG_ID = "_id";
	public static final String LOG_ENTRY_RIG_LOG_ENTRY = "log_entry";
	public static final String LOG_ENTRY_RIG_RIG = "rig";
	public static final String[] LOG_ENTRY_RIG_ALL_COLUMNS =
	{
		LOG_ENTRY_RIG_ID,
		LOG_ENTRY_RIG_LOG_ENTRY,
		LOG_ENTRY_RIG_RIG
	};
	
	// log entry image table
	public static final String LOG_ENTRY_IMAGE_TABLE = "log_entry_image";
	public static final String LOG_ENTRY_IMAGE_ID = "_id";
	public static final String LOG_ENTRY_IMAGE_LOG_ENTRY = "log_entry";
	public static final String LOG_ENTRY_IMAGE_IMAGE_TYPE = "image_type";
	public static final String LOG_ENTRY_IMAGE_MD5 = "md5";
	public static final String LOG_ENTRY_IMAGE_FILENAME = "filename";
	public static final String[] LOG_ENTRY_IMAGE_ALL_COLUMNS =
	{
		LOG_ENTRY_IMAGE_ID,
		LOG_ENTRY_IMAGE_LOG_ENTRY,
		LOG_ENTRY_IMAGE_IMAGE_TYPE,
		LOG_ENTRY_IMAGE_MD5,
		LOG_ENTRY_IMAGE_FILENAME
	};
	
	// skydive type table
	public static final String SKYDIVE_TYPE_TABLE = "skydive_type";
	public static final String SKYDIVE_TYPE_ID = "_id";
	public static final String SKYDIVE_TYPE_ACTIVE = "active";
	public static final String SKYDIVE_TYPE_NAME = "name";
	public static final String SKYDIVE_TYPE_DEFAULT = "_default";
	public static final String SKYDIVE_TYPE_NOTES = "notes";
	public static final String SKYDIVE_TYPE_FREEFALL_PROFILE = "freefall_profile";
	public static final String SKYDIVE_TYPE_LAST_MODIFIED_UTC = "last_modified_utc";
	public static final String SKYDIVE_TYPE_UNIQUE_ID = "unique_id";
	public static final String[] SKYDIVE_TYPE_ALL_COLUMNS =
	{
		SKYDIVE_TYPE_ID,
		SKYDIVE_TYPE_ACTIVE,
		SKYDIVE_TYPE_NAME,
		SKYDIVE_TYPE_DEFAULT,
		SKYDIVE_TYPE_NOTES,
		SKYDIVE_TYPE_FREEFALL_PROFILE,
		SKYDIVE_TYPE_LAST_MODIFIED_UTC,
		SKYDIVE_TYPE_UNIQUE_ID
	};
	
	// location table
	public static final String LOCATION_TABLE = "location";
	public static final String LOCATION_ID = "_id";
	public static final String LOCATION_ACTIVE = "active";
	public static final String LOCATION_NAME = "name";
	public static final String LOCATION_HOME = "home";
	public static final String LOCATION_NOTES = "notes";
	public static final String LOCATION_LAST_MODIFIED_UTC = "last_modified_utc";
	public static final String LOCATION_UNIQUE_ID = "unique_id";
	public static final String[] LOCATION_ALL_COLUMNS =
	{
		LOCATION_ID,
		LOCATION_ACTIVE,
		LOCATION_NAME,
		LOCATION_HOME,
		LOCATION_NOTES,
		LOCATION_LAST_MODIFIED_UTC,
		SKYDIVE_TYPE_UNIQUE_ID
	};
	
	// aircraft table
	public static final String AIRCRAFT_TABLE = "aircraft";
	public static final String AIRCRAFT_ID = "_id";
	public static final String AIRCRAFT_ACTIVE = "active";
	public static final String AIRCRAFT_NAME = "name";
	public static final String AIRCRAFT_DEFAULT = "_default";
	public static final String AIRCRAFT_NOTES = "notes";
	public static final String AIRCRAFT_LAST_MODIFIED_UTC = "last_modified_utc";
	public static final String AIRCRAFT_UNIQUE_ID = "unique_id";
	public static final String[] AIRCRAFT_ALL_COLUMNS =
	{
		AIRCRAFT_ID,
		AIRCRAFT_ACTIVE,
		AIRCRAFT_NAME,
		AIRCRAFT_DEFAULT,
		AIRCRAFT_NOTES,
		AIRCRAFT_LAST_MODIFIED_UTC,
		AIRCRAFT_UNIQUE_ID
	};
	
	// rig table
	public static final String RIG_TABLE = "rig";
	public static final String RIG_ID = "_id";
	public static final String RIG_ACTIVE = "active";
	public static final String RIG_NAME = "name";
	public static final String RIG_PRIMARY = "_primary";
	public static final String RIG_ARCHIVED = "archived";
	public static final String RIG_NOTES = "notes";
	public static final String RIG_LAST_MODIFIED_UTC = "last_modified_utc";
	public static final String RIG_UNIQUE_ID = "unique_id";
	public static final String[] RIG_ALL_COLUMNS =
	{
		RIG_ID,
		RIG_ACTIVE,
		RIG_NAME,
		RIG_PRIMARY,
		RIG_ARCHIVED,
		RIG_NOTES,
		RIG_LAST_MODIFIED_UTC,
		RIG_UNIQUE_ID
	};
	
	// component table
	public static final String RIG_COMPONENT_TABLE = "rig_component";
	public static final String RIG_COMPONENT_ID = "_id";
	public static final String RIG_COMPONENT_RIG = "rig";
	public static final String RIG_COMPONENT_NAME = "name";
	public static final String RIG_COMPONENT_SERIAL_NUMBER = "serial_number";
	public static final String RIG_COMPONENT_NOTES = "notes";
	public static final String[] RIG_COMPONENT_ALL_COLUMNS =
	{
		RIG_COMPONENT_ID,
		RIG_COMPONENT_RIG,
		RIG_COMPONENT_NAME,
		RIG_COMPONENT_SERIAL_NUMBER,
		RIG_COMPONENT_NOTES
	};
	
	// rig reminder table
	public static final String RIG_REMINDER_TABLE = "rig_reminder";
	public static final String RIG_REMINDER_ID = "_id";
	public static final String RIG_REMINDER_RIG = "rig";
	public static final String RIG_REMINDER_NAME = "name";
	public static final String RIG_REMINDER_LAST_COMPLETED_DATE = "last_completed_date";
	public static final String RIG_REMINDER_INTERVAL = "interval";
	public static final String RIG_REMINDER_INTERVAL_UNIT = "interval_unit";
	public static final String[] RIG_REMINDER_ALL_COLUMNS =
	{
		RIG_REMINDER_ID,
		RIG_REMINDER_RIG,
		RIG_REMINDER_NAME,
		RIG_REMINDER_LAST_COMPLETED_DATE,
		RIG_REMINDER_INTERVAL,
		RIG_REMINDER_INTERVAL_UNIT
	};
	
	// settings table
//	public static final String SETTINGS_TABLE = "settings";
//	public static final String SETTINGS_UNIT_OF_MEASURE = "unit_of_measure";
//	public static final String SETTINGS_EXIT_ALTITUDE = "default_exit_altitude";
//	public static final String SETTINGS_DEPLOYMENT_ALTITUDE = "default_deployment_altitude";
//	public static final String SETTINGS_USE_LIGHT_THEME = "use_light_theme";
//	public static final String SETTINGS_USE_EXTERNAL_STORAGE = "use_external_storage";
//	public static final String[] SETTINGS_ALL_COLUMNS =
//	{
//		SETTINGS_UNIT_OF_MEASURE,
//		SETTINGS_EXIT_ALTITUDE,
//		SETTINGS_DEPLOYMENT_ALTITUDE,
//		SETTINGS_USE_LIGHT_THEME,
//		SETTINGS_USE_EXTERNAL_STORAGE
//	};
	
	// logbook history table
//	public static final String LOGBOOK_HISTORY_TABLE = "logbook_history";
//	public static final String LOGBOOK_HISTORY_FREEFALL_TIME = "freefall_time";
//	public static final String LOGBOOK_HISTORY_CUTAWAYS = "cutaways";
//	public static final String[] LOGBOOK_HISTORY_ALL_COLUMNS =
//	{
//		LOGBOOK_HISTORY_FREEFALL_TIME,
//		LOGBOOK_HISTORY_CUTAWAYS
//	};
    
	// date formatter
	private static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	private static DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	private static final String CREATE_LOG_ENTRY_TABLE =
		"create table log_entry (" +
		"_id integer primary key autoincrement, " +
		"jump_number integer not null, " +
		"date text not null, " +
		"distance_to_target integer, " +
		"altitude_unit text not null, " +
		"exit_altitude integer, " +
		"deployment_altitude integer, " +
		"freefall_time_seconds integer, " +
		"skydive_type integer, " +
		"location integer, " +
		"aircraft integer, " +
		"cutaway integer not null, " +
		"notes text, " +
		"last_modified_utc text, " +
		"last_signature_utc text, " +
		"unique_id text not null default 0 , " +
		"foreign key (skydive_type) references skydive_type(_id)" +
		"foreign key (location) references location(_id)" +
		"foreign key (aircraft) references aircraft(_id)" +
		");";
	
	private static final String CREATE_SIGNATURE_TABLE =
		"create table signature (" +
		"_id integer primary key autoincrement, " +
		"filename text not null, " +
		"md5 text not null, " +
		"license text not null);";
	
	private static final String CREATE_LOG_ENTRY_SIGNATURE_TABLE =
		"create table log_entry_signature (" +
		"_id integer primary key autoincrement, " +
		"log_entry integer not null, " +
		"signature integer not null, " +
		"foreign key (log_entry) references log_entry(_id), " +
		"foreign key (signature) references signature(_id)" +
		");";
	
	private static final String CREATE_LOG_ENTRY_RIG_TABLE =
		"create table log_entry_rig (" +
		"_id integer primary key autoincrement, " +
		"log_entry integer not null, " +
		"rig integer not null, " +
		"foreign key (log_entry) references log_entry(_id), " +
		"foreign key (rig) references rig(_id)" +
		");";
	
	public static final String CREATE_LOG_ENTRY_IMAGE_TABLE =
			"create table log_entry_image (" +
			"_id integer primary key autoincrement, " +
			"log_entry integer not null, " +
			"image_type text not null, " +
			"md5 text not null, " +
			"filename text not null, " +
			"foreign key (log_entry) references log_entry(_id)" +
			");";
	
	private static final String CREATE_SKYDIVE_TYPE_TABLE =
		"create table skydive_type (" +
		"_id integer primary key autoincrement, " +
		"active integer not null, " +
		"name text not null, " +
		"_default integer not null, " +
		"notes text, " +
		"freefall_profile text, " + 
		"last_modified_utc text, " +
		"unique_id text not null default 0);";
	
	private static final String CREATE_LOCATION_TABLE =
		"create table location (" +
		"_id integer primary key autoincrement, " +
		"active integer not null, " +
		"name text not null, " +
		"home integer not null, " +
		"notes text, " + 
		"last_modified_utc text, " +
		"unique_id text not null default 0);";
	
	private static final String CREATE_AIRCRAFT_TABLE =
		"create table aircraft (" +
		"_id integer primary key autoincrement, " +
		"active integer not null, " +
		"name text not null, " +
		"_default integer not null, " +
		"notes text, " +
		"last_modified_utc text, " +
		"unique_id text not null default 0);";
	
	private static final String CREATE_RIG_TABLE =
		"create table rig (" +
		"_id integer primary key autoincrement, " +
		"active integer not null, " +
		"name text not null, " +
		"_primary integer not null, " +
		"archived integer not null, " +
		"notes text, " +
		"last_modified_utc text, " +
		"unique_id text not null default 0);";
	
	private static final String CREATE_RIG_COMPONENT_TABLE =
		"create table rig_component (" +
		"_id integer primary key autoincrement, " +
		"rig integer not null, " +
		"name text not null, " + 
		"serial_number text, " + 
		"notes text, " + 
		"foreign key (rig) references rig(_id)" +
		");";
	
	private static final String CREATE_RIG_REMINDER_TABLE =
		"create table rig_reminder (" +
		"_id integer primary key autoincrement, " +
		"rig integer not null, " +
		"name text not null, " +
		"last_completed_date text not null, " +
		"interval integer not null, " +
		"interval_unit text not null, " +
		"foreign key (rig) references rig(_id)" +
		");";
	
//	private static final String CREATE_SETTINGS_TABLE =
//		"create table settings (" +
//		"unit_of_measure text not null, " +
//		"default_exit_altitude integer not null, " +
//		"default_deployment_altitude integer not null, " +
//		"use_light_theme integer not null, " +
//		"use_external_storage integer not null" +
//		");";
	
//	private static final String CREATE_LOGBOOK_HISTORY_TABLE =
//		"create table logbook_history (" +
//		"freefall_time int not null, " +
//		"cutaways int not null" +
//		");";
	
	private Context context;

	public Database(Context context)
	{
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		// create tables
		db.execSQL(CREATE_LOCATION_TABLE);
		db.execSQL(CREATE_AIRCRAFT_TABLE);
		db.execSQL(CREATE_SKYDIVE_TYPE_TABLE);
		db.execSQL(CREATE_RIG_TABLE);
		db.execSQL(CREATE_RIG_COMPONENT_TABLE);
		db.execSQL(CREATE_RIG_REMINDER_TABLE);
		db.execSQL(CREATE_SIGNATURE_TABLE);
		db.execSQL(CREATE_LOG_ENTRY_TABLE);
		db.execSQL(CREATE_LOG_ENTRY_RIG_TABLE);
		db.execSQL(CREATE_LOG_ENTRY_SIGNATURE_TABLE);
		db.execSQL(CREATE_LOG_ENTRY_IMAGE_TABLE);
//		db.execSQL(CREATE_SETTINGS_TABLE);
//		db.execSQL(CREATE_LOGBOOK_HISTORY_TABLE);
		// insert default skydive types
		Data.insertDefaultSkydiveTypes(db);
		// insert default data
		Data.insertDefaltAircrafts(db);
		// insert default settings
//		Data.insertDefaultSettings(db);		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		if (oldVersion < 2)
		{
			DatabaseUpgrade_1_2 upgrade = new DatabaseUpgrade_1_2();
			upgrade.doUpgrade(db);
		}
		if (oldVersion < 3)
		{
			DatabaseUpgrade_2_3 upgrade = new DatabaseUpgrade_2_3();
			upgrade.doUpgrade(db, context);
		}
	}
	
	public static String max(String columnName)
	{
		return "max(" + columnName + ")";
	}
	
	public static boolean isNull(Cursor cursor, String columnName)
	{
		return cursor.isNull(cursor.getColumnIndex(columnName));
	}
	
	public static Integer getInteger(Cursor cursor, String columnName)
	{
		if (isNull(cursor, columnName))
			return null;
		else
			return cursor.getInt(cursor.getColumnIndex(columnName));
	}
	
	public static Long getLong(Cursor cursor, String columnName)
	{
		if (isNull(cursor, columnName))
			return null;
		else
			return cursor.getLong(cursor.getColumnIndex(columnName));
	}
	
	public static String getString(Cursor cursor, String columnName)
	{
		return cursor.getString(cursor.getColumnIndex(columnName));
	}
	
	public static Date getDate(Cursor cursor, String columnName)
	{
		String dateStr = getString(cursor, columnName);
		return stringToDate(dateStr);
	}
	
	public static Date getDateTime(Cursor cursor, String columnName)
	{
		String dateStr = getString(cursor, columnName);
		return stringToDateTime(dateStr);
	}
	
	public static Boolean getBoolean(Cursor cursor, String columnName)
	{
		if (isNull(cursor, columnName))
			return null;
		else
			return getInteger(cursor, columnName) > 0;
	}
	
	public static byte[] getBinaryData(Cursor cursor, String columnName)
	{
		return cursor.getBlob(cursor.getColumnIndex(columnName));
	}
	
	public static String dateToString(Date date)
	{
		return dateFormat.format(date);
	}
	
	public static String dateTimeToString(Date dateTime)
	{
		return dateTimeFormat.format(dateTime);
	}
	
	public static Date stringToDate(String str)
	{
		try
		{
			if (str == null)
				return null;
			else
				return dateFormat.parse(str);
		}
		catch (ParseException ex)
		{
			Log.e(Database.class.getCanonicalName(), "Could not parse date: " + str);
			return null;
		}
	}
	
	public static Date stringToDateTime(String str)
	{
		try
		{
			if (str == null)
				return new Date(0);
			else
				return dateTimeFormat.parse(str);
		}
		catch (ParseException ex)
		{
			Log.e(Database.class.getCanonicalName(), "Could not parse date: " + str);
			return new Date(0);
		}
	}
}
